from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

HOSTNAME = "127.0.0.1"
USERNAME = "root"
PASSWORD = "root"
PORT = "3306"
DATABASE = "flask_database"

app = Flask(__name__)

app.config[
    "SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"

db = SQLAlchemy(app)

with app.app_context():
    with db.engine.connect() as conn:
        rs = conn.execute(text("select 1"))
        print(rs.fetchone())


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(100), nullable=False)
    address = db.Column(db.String(255), nullable=False)


class Article(db.Model):
    __tablename__ = "article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100))
    content = db.Column(db.Text)

    # 添加作者的外键
    author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    # 给user模型自动添加articles属性用来获取文章列表
    author = db.relationship("User", backref="articles")


with app.app_context():
    db.create_all()


@app.route('/')
def hello_world():  # put application's code here
    return 'Hello World!'


@app.route("/article/add")
def article_add():
    article1 = Article(name="python开小白到精通", content="学习python需要,首先xxxx")
    article2 = Article(name="python框架flask全栈开发", content="flask全栈开发xxxx")
    article1.author = User.query.get(1)
    article2.author = User.query.get(1)
    db.session.add_all([article1, article2])
    db.session.commit()
    return "<h1>文章添加成功</h1>"


@app.route("/article/query")
def article_query():
    username = request.args.get("username", default="zhangsan")
    user = User.query.filter_by(username=username).first()
    art_list = []
    for art in user.articles:
        art_list.append({key: value for key, value in art.__dict__.items() if not key.startswith("_")})

    print(art_list)

    return "123"


if __name__ == '__main__':
    app.run()
